SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROC [dbo].[PReqWorkForPrepodSELECT]
@ik_year_uch_pl INT,
@ik_sem_type INT,
@iK_disc INT,
@iK_vid_zanyat INT,
@Ik_grup INT,
@nCode NUMERIC 
AS
SELECT distinct
Ik_ListReq[idЛиста требований],
Ik_ReqWork[idТребования к работе],
Ik_RefReq[idНазвания требования],
NameReq[Название требования],
Expdoc[Расширение файла]
FROM dbo.Person pr
inner join dbo.Prepod P on pr.nCode = p.nCode
inner join dbo.Relation_kafedra_prep Rp on p.itab_n=Rp.itab_n 
inner join dbo.Prepod_plan_nagr ppn on rp.ik_id_prepod = ppn.ik_id_prepod
inner join dbo.Content_plan_nagr cpn on ppn.ik_content_plan_nagr = cpn.ik_content_plan_nagr
inner join dbo.vid_zaniat vz on cpn.ik_vid_zanyat = vz.iK_vid_zanyat
inner join dbo.Plan_nagr pn on cpn.ik_plan_nagr = pn.ik_plan_nagr
inner join dbo.SemesterType ST on pn.ik_sem_type=ST.ik_sem_type
inner join dbo.Year_uch_pl Yup on pn.ik_year=Yup.ik_year_uch_pl
inner join dbo.kafedra kf on pn.ik_kaf = kf.ik_kaf
inner join dbo.Content_UchPl cup on kf.ik_kaf = cup.ik_kaf
inner join dbo.discpln ds on cpn.ik_disc = ds.iK_disc
inner join dbo.Calc_unit_semestr cus on ppn.ik_calc_unit = cus.ik_calc_unit
inner join dbo.Calc_unit_grup cug on cus.ik_calc_unit = cug.ik_calc_unit
inner join dbo.Grup g on cug.Ik_grup = g.Ik_grup
INNER JOIN dbo.Uch_pl Up 
ON G.Ik_uch_plan=Up.ik_uch_plan 
full join 
(select cu.ik_upContent,
Expdoc, NameReq, RW.Ik_ReqWork, rr.Ik_RefReq, LR.Ik_ListReq from dbo.Content_UchPl cu
INNER JOIN dbo.ReqWork RW 
ON CU.ik_upContent=RW.Ik_upContent
INNER JOIN dbo.ListReq LR 
ON RW.Ik_ReqWork=LR.Ik_ReqWork 
INNER JOIN dbo.RefReq RR 
ON LR.Ik_RefReq=RR.Ik_RefReq
) b
ON cup.ik_upContent=b.ik_upContent
WHERE @iK_disc=ds.iK_disc AND @iK_vid_zanyat=vz.iK_vid_zanyat AND @ik_year_uch_pl=Yup.ik_year_uch_pl 
AND pr.nCode=@nCode AND @Ik_grup=g.Ik_grup 
AND @ik_sem_type=ST.ik_sem_type
GO
